package com.example.demo.sql;

import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;

import java.util.ArrayList;
import java.util.List;


public class Main {

    public static void main(String[] args) {
        // 创建表对象
        Table table1 = new Table("users").withAlias(new Alias("u"));
        Table table2 = new Table("orders").withAlias(new Alias("o"));

        // 创建列对象
        Column column1 = new Column(table1, "id");
        Column column2 = new Column(table1, "name");
        Column column3 = new Column(table2, "order_id");

        // 创建选择项
        SelectItem<Column> selectItem1 = new SelectItem<>(column1, new Alias("user_id"));
        SelectItem<Column> selectItem2 = new SelectItem<>(column2, new Alias("user_name"));
        SelectItem<Column> selectItem3 = new SelectItem<>(column3, new Alias("order_id"));


        // 添加聚合指标
        Function function = new Function();
        function.setName("COUNT");
        function.setParameters(column2);
        SelectItem<Function> countOrdersItem = new SelectItem<>(function, new Alias("order_count"));


        // 创建 PlainSelect 对象
        PlainSelect plainSelect = new PlainSelect();
        plainSelect.addSelectItems(selectItem1, selectItem2, selectItem3, countOrdersItem);
        plainSelect.setFromItem(table1);

        // 创建左连接
        Join join = new Join();
        join.setRightItem(table2);
        join.setLeft(true);

        // 创建连接条件
        Expression onExpression = new EqualsTo(new Column(table1, "id"), new Column(table2, "user_id"));
        join.addOnExpression(onExpression);

        List<Join> joins = new ArrayList<>();
        joins.add(join);
        // 添加连接到 PlainSelect
        plainSelect.setJoins(joins);


        // 创建 WHERE 条件
        Expression whereCondition1 = new EqualsTo(new Column(table1, "name"), new StringValue("John Doe"));
        Expression whereCondition2 = new EqualsTo(new Column(table1, "age"), new LongValue(30));
        Expression whereCondition3 = new EqualsTo(new Column(table2, "status"), new StringValue("active"));

        //使用 OrExpression 组合第二个和第三个条件，并用括号括起来
        Expression orExpression = new OrExpression(whereCondition2, whereCondition3);
        Expression parenthesizedOrExpression = new Parenthesis(orExpression);

        // 使用 AndExpression 组合第一个条件和上述组合的条件
        Expression whereExpression = new AndExpression(whereCondition1, parenthesizedOrExpression);


        // 设置 WHERE 条件
        plainSelect.setWhere(whereExpression);

        // 添加分组指标
        // 按 user_id 分组
        plainSelect.addGroupByColumnReference(column1);
        plainSelect.addGroupByColumnReference(column2);


        // 打印生成的 SQL
        System.out.println(plainSelect.toString());

    }

}
